Aurora PostgreSQLでpgAuditを有効にして監査ログを取得する

quiver

2022.03.29

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Aurora PostgreSQLで監査ログを取得する場合

PostgreSQL ネイティブのツールを利用 : pgAudit
AWS の仕組み活用 : Database Activity Stream

の2パターンがあります。

本ブログでは、前者の pgAudit を利用して監査ログを取得する方法を紹介します。

後者はAWSの知識がある程度要求されるのに対し、前者は pgAudit(PostgreSQL) の既存のノウハウを活かしやすいというメリットがあります。

内容的には、次のナレッジセンターの手順をベースに、S3へのアーカイブ処理を追加しています。

PostgreSQL を実行している Amazon RDS DB インスタンスを pgaudit 拡張機能を使用して監査するにはどうすればよいですか?

Database Activity Stream 方式は次の記事を参照ください。

検証環境

Aurora PostgreSQL 13.6

1. ロールの作成

PostgreSQL に接続し pgAudit 用ロール(rds_pgaudit)を作成します。

postgres=> CREATE ROLE rds_pgaudit;
CREATE ROLE

2. クラスターパラメーターグループの変更

pgAudit を利用するには、パラメーターグループの変更が必要です。

デフォルトとは異なるパラメーターグループに対して、以下の変更を実施してください。

pgaudit.log : ログ出力するクラス。allやddlなど
pgaudit.role : rds_pgaudit
shared＿preload_libraries : pgauditを追加

3. Aurora PostgreSQL の設定変更

Aurora PostgreSQL クラスターに以下の変更を行います。

先程作成したクラスターパラメーターグループを適用
CloudWatch Logsへのログ出力を有効化

4. Auroraクラスターの再起動

パラメーターグループの変更を反映させるため、クラスターを再起動します。

5. 監査用エクステンションを有効化

監査用エクステンション(pgaudit)を作成します。

postgres=> CREATE EXTENSION pgaudit;
CREATE EXTENSION

6. CloudWatch Logsの確認

pgaudit.log で指定したクラスに対応する SQL(create table foo(a int); など) を実行し、CloudWatch Logsにログ出力されるか確認します。

pgAudit のログは、次のログのようにログメッセージが :LOG: AUDIT: で接頭しています。

2022-03-25 13:41:29 UTC:172.31.40.77(53806):postgres@test:[3152]:LOG:  AUDIT: SESSION,7,1,DDL,CREATE TABLE,TABLE,public.foo,create table foo(a int);,<not logged>

7. 監査ログアーカイブ用S３バケットの作成

監査ログを長期保存し、Athenaなどから問い合わせられるよう、S3に保存します。

そのための S3 バケットを作成します。

8. Amazon Kinesis Data Firehoseの作成

Amazon Kinesis Data Firehoseはストリームデータを S3、Amazon OpenSearch Service等様々なサービスにリアルタイム配信するフルマネージドサービスです。

CloudWatch Logsのサブスクリプションフィルターを利用し、CloudWatch Logs ログをKinesis Firehose経由でS3に保存します。

そのための Firehose ストリームを作成します。

合わせて、FirehoseがS3にオブジェクトを保存するためのIAMポリシーも必要です。

必要なIAM ポリシーは次のページにまとまっています。

https://docs.aws.amazon.com/firehose/latest/dev/controlling-access.html#using-iam-s3

今回のケースでは、ミニマムとして

S3 への更新権限(特に s3:PutObject)
CloudWatch Logs への Firehose の実行ログ出力権限(特に logs:PutLogEvents)

などが必要です。

9. CloudWatch Logsのサブスクリプションフィルターの作成

CloudWatch LogsからはFirehoseサブスクリプションフィルター経由でS3にログ転送します。

まず、CloudWatch LogsがFirehoseにデータ送信するでためのIAMポリシーも必要です。

具体的には

firehose:PutRecord
firehose:PutRecordBatch

などが必要です。

次に、PostgreSQLのログが出力されているロググループを選択し、「Create Kinesis Firehose subscription filter」からサブスクリプションフィルターを作成します。

Choose destination : 作成した Firehose ストリームを指定
Grant permission : 作成した IAM ロールを指定
Configure log format and filters
- Log format : Other
- Subscription filter pattern : ":LOG: AUDIT:"
- Subscription filter name : 任意

pgAudit のログは ":LOG: AUDIT:" で接頭しているため、フィルターパターンにこの文字列を指定します。

Test pattern からフィルターパターンを検証し、 pgAudit ログだけにフィルターされていることを確認してください。

10. S3 ログの確認

再度 pgAudit 対象のSQLを実行します。

test=> create table bar(b int);
CREATE TABLE

CloudWatch Logs には次のように出力されました。

2022-03-25 13:52:29 UTC:172.31.40.77(53806):postgres@test:[3152]:LOG:  AUDIT: SESSION,8,1,DDL,CREATE TABLE,TABLE,public.bar,create table bar(b int);,<not logged>

サブスクリプションフィルターにより、監査ログだけが S3 に出力されていることを確認します。

$ aws s3 cp \
  s3://BUCKET/pgaudit/2022/03/25/13/PUT-S3-E78KX-1-2022-03-25-13-51-44-xxx - |
  gzip -d | jq .
{
  "messageType": "CONTROL_MESSAGE",
  "owner": "CloudwatchLogs",
  "logGroup": "",
  "logStream": "",
  "subscriptionFilters": [],
  "logEvents": [
    {
      "id": "",
      "timestamp": 1648216304378,
      "message": "CWL CONTROL MESSAGE: Checking health of destination Firehose."
    }
  ]
}
{
  "messageType": "DATA_MESSAGE",
  "owner": "123456",
  "logGroup": "/aws/rds/cluster/pg/postgresql",
  "logStream": "pg-instance-1.0",
  "subscriptionFilters": [
    "pgaudit"
  ],
  "logEvents": [
    {
      "id": "36756452831101423841029483126659836349813264428266815488",
      "timestamp": 1648216349000,
      "message": "2022-03-25 13:52:29 UTC:172.31.40.77(53806):postgres@test:[3152]:LOG:  AUDIT: SESSION,8,1,DDL,CREATE TABLE,TABLE,public.bar,create table bar(b int);,<not logged>"
    }
  ]
}

S3のログ形式を変更したい場合